package org.example.dao;

import org.example.model.borrow;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;


@Repository
public class BorrowDaoImp implements BorrowDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Override
    public boolean isExistById(String id) {
        String sql = "select count(*) from borrow where id=?";
        return jdbcTemplate.queryForObject(sql, Integer.class, id) > 0;
    }
    @Override
    @Transactional(propagation = Propagation.SUPPORTS)
    public int borrowBook(borrow borrow) {
        String sql = "insert into borrow(id, user_id, book_id, borrow_date) VALUE (?,?,?,?)";
        return jdbcTemplate.update(sql,
                borrow.getId(),
                borrow.getUser_id(),
                borrow.getBook_id(),
                new Date()
        );
    }

    @Override
    @Transactional(propagation = Propagation.SUPPORTS)
    public int returnBook(borrow borrow) {
        String sql = "update borrow set " +
                "return_date=? " +
                "where id=?";
        return jdbcTemplate.update(sql,
                new Date(),
                borrow.getId()
        );
    }

    @Override
    public int update(borrow borrow) {
        String sql = "update borrow set " +
                "user_id=?," +
                "book_id=?," +
                "borrow_date=?," +
                "return_date=? " +
                "where id=?";
        return jdbcTemplate.update(sql,
                borrow.getUser_id(),
                borrow.getBook_id(),
                borrow.getBorrow_date(),
                borrow.getReturn_date(),
                borrow.getId()
        );
    }

    @Override
    public borrow queryById(String borrow_id) {
        borrow_id = borrow_id.replaceAll(".*([';]+|(--)+).*", " ");
        String sql = "select id,user_id,book_id,borrow_date,return_date from borrow " +
                "where id = " + borrow_id;
        List<borrow> temp = jdbcTemplate.query(sql, this::transform);
        if (temp.isEmpty()) return null;
        return temp.get(0);
    }

    @Override
    public List<borrow> queryAll() {
        String sql = "select id,user_id,book_id,borrow_date,return_date from borrow ";
        return jdbcTemplate.query(sql, this::transform);
    }

    @Override
    public List<borrow> queryByBook(String book_id) {
        book_id = book_id.replaceAll(".*([';]+|(--)+).*", " ");
        String sql = "select id,user_id,book_id,borrow_date,return_date from borrow " +
                "where book_id=" + book_id + " and return_date is null";
        return jdbcTemplate.query(sql, this::transform);
    }

    @Override
    public List<borrow> queryByUser(String user_id) {
        user_id = user_id.replaceAll(".*([';]+|(--)+).*", " ");
        String sql = "select id,user_id,book_id,borrow_date,return_date from borrow " +
                "where user_id=" + user_id + " and return_date is null";
        return jdbcTemplate.query(sql, this::transform);
    }

    @Override
    public List<borrow> queryByBookAndUser(String book_id, String user_id) {
        user_id = user_id.replaceAll(".*([';]+|(--)+).*", " ");
        book_id = book_id.replaceAll(".*([';]+|(--)+).*", " ");
        String sql = "select id,user_id,book_id,borrow_date,return_date from borrow " +
                "where book_id = " + book_id + " and user_id = " + user_id + " and return_date is null";
        return jdbcTemplate.query(sql, this::transform);
    }

    private borrow transform(ResultSet rs, int rowNum) throws SQLException {
        borrow borrow = new borrow();
        borrow.setId(rs.getString("id"));
        borrow.setUser_id(rs.getString("user_id"));
        borrow.setBook_id(rs.getString("book_id"));
        borrow.setBorrow_date(rs.getDate("borrow_date"));
        borrow.setReturn_date(rs.getDate("return_date"));
        return borrow;
    }
}
